package com.java.control.impl;

import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.*;
import java.util.*;
import java.text.SimpleDateFormat;


import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.ModelAndView;

import com.java.common.DateUtil;
import com.java.common.FileStorage;
import com.java.common.ProductFileService;
import com.java.common.SessionInfo;
import com.java.control.AbstractAdminController;
import com.java.entity.AddressBook;
import com.java.entity.Company;
import com.java.entity.FactoryPayment;
import com.java.entity.IncomeExpenses;
import com.java.entity.OrderForm;
import com.java.entity.Product;
import com.java.entity.QIndex;
import com.java.entity.QList;
import com.java.entity.ReceiveExpress;
import com.java.entity.SendExpress;
import com.java.entity.Supplier;
import com.java.entity.SupplierQuotation;
import com.java.service.AddressBookService;
import com.java.service.CompanyService;
import com.java.service.OrderService;
import com.java.service.ProductService;
import com.java.service.QuotationService;
import com.java.service.ReceiveService;
import com.java.service.SendService;
import com.java.service.SupplierQuotationService;
import com.java.service.SupplierService;


/**
 * 导出Excel文件
 * 
 * @author
 * 
 */

public class ExportExcelController extends AbstractAdminController {

	private static HSSFFont fontStyle = null;
	private static HSSFCellStyle cellStyle = null;
	/*设置字体格式*/  
	public static HSSFFont getFontStyle(HSSFWorkbook wb) {
		fontStyle = wb.createFont();
		fontStyle.setFontName("宋体");
		fontStyle.setFontHeightInPoints((short)20);
		fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		return fontStyle;  
	}  
	
	protected ModelAndView executeRequest(HttpServletRequest request,HttpServletResponse response)	throws Exception{

		String ids = request.getParameter("ids");
		String type = request.getParameter("t");
		String file = DateUtil.getDateTimeFormString("yyyy.MM.dd",new Date());
		switch(Integer.valueOf(type)){
		case 0:supplierQuotation(response,ids);break;
		case 1:addressBook(response,ids,"AddressBook-"+file+".xls");break;
		case 2:product(response,ids,"Product-"+file+".xls");break;
		case 3:supplier(response,ids,"Supplier-"+file+".xls");break;
		case 4:quotation(response,ids,"SupplierQuotation-"+file+".xls");break;
		case 5:order(response,ids,"MAX"+file.substring(0, 4)+".xls");break;
		case 6:send(response,ids,"Send-"+file+".xls");break;
		case 7:receive(response,ids,"Rreceive-"+file+".xls");break;
		case 8:
			SessionInfo user = getSessionInfo(request);
			Company company = this.companyService.getCompanyById(1l);
			qlist(response,company,ids,file+"-Quotation-Maxofei.xls");
			break;
		default:
		}
				
		return null;

	}

	/**
	 * 输了excel表格
	 * @param response
	 * @param workbook
	 * @param fileName
	 */
	private static void outExcel(HttpServletResponse response,HSSFWorkbook workbook,String fileName){        
		try{
			String saveFileName = new String(fileName.getBytes("utf-8"), "iso8859-1");		
//			response.setContentType("application/octet-stream;charset=UTF-8");
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment;filename=" + saveFileName);			 
			BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream());
			workbook.write(out);
			out.close();			
		}catch(Exception e){
			e.printStackTrace();
		}		
	}
	
	/**
     * 生成excel的通用模版
	 * @param <T>
     *
     * @param response  http响应,设置生成的文件类型,文件头编码方式和文件名,以及输出
     * @param data      数据内容，List类型，里面的每个节点是String[]型
     * @param headers   表头字符串数组 String[]
     * @param sheetName 表名
     * @param fileName  保存文件名
     */
    public static <T> void ExportToExcel(HttpServletResponse response, Collection<T> dataset, String[] headers, String sheetName, String fileName) throws Exception {
    	// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 在workbook中添加一个表
		HSSFSheet sheet = workbook.createSheet(sheetName);
		
		// 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);
       					
		// 生成表头样式
        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        
        // 设置表头字体
        HSSFFont headFont = workbook.createFont();
        headFont.setColor(HSSFColor.VIOLET.index);
        headFont.setFontHeightInPoints((short) 12);
        headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        headStyle.setFont(headFont);
        				
		//设置内容单元格样式		
		HSSFCellStyle style = workbook.createCellStyle();
		style.setWrapText(true);
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        
		//style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        //设置内容单元格字体
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);		
		style.setFont(font);
				
		//生成表格标题行
		HSSFRow row = sheet.createRow(0);//0行
		row.setHeight((short) 300);
		for(int i=0;i<headers.length;i++){
			HSSFCell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
		}
		
		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (short i = 0; i < fields.length; i++) {
            	
            }
        }
        
        outExcel(response,workbook,fileName);
    }
    
	/**
	 * 供应商报价信息
	 * @param request
	 * @param response
	 */
	private void supplierQuotation(HttpServletResponse response,String ids) throws Exception {
		List<SupplierQuotation> data = this.supplierQuotationService.getSupplierQuotationByIds(ids);
		// 创建workbook
		HSSFWorkbook wb = new HSSFWorkbook();
		// 添加sheet
		HSSFSheet sheet = wb.createSheet("sheet1");
		// 设置每列宽度
		sheet.setColumnWidth(0, 2000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 10000);
		sheet.setColumnWidth(4, 13000);
		
		//设置表头和样式
		HSSFRow row = sheet.createRow(0);//0行
		row.setHeight((short) 450);
		
		//设置单元格样式
		HSSFCell cellTitle;
		HSSFCellStyle style = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		
		cellTitle = row.createCell(0);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("记录时间");
		cellTitle = row.createCell(1);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("会员等级");
		cellTitle = row.createCell(2);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("部门");
		cellTitle = row.createCell(3);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("标题");
		cellTitle = row.createCell(4);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("详细信息");
		
		SupplierQuotation pojo;
		HSSFCell cell;
		for (short i = 1; i <= data.size(); i++) {
			row = sheet.createRow(i);
			row.setHeight((short) 350);
			pojo = data.get(i - 1);
			
			cell = row.createCell(0);
			cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", pojo.getQuoteTime())));

			cell = row.createCell(1);
			cell.setCellValue(new HSSFRichTextString("AAA"));

			cell = row.createCell(2);
			cell.setCellValue(new HSSFRichTextString("BBB"));
			
			cell = row.createCell(3);
			cell.setCellValue(new HSSFRichTextString("CCC"));
			
			cell = row.createCell(4);
			cell.setCellValue(new HSSFRichTextString("DDD"));
		}
		
		String fileName = "供应商报价信息";		
		outExcel(response,wb,fileName);
	}
	
	private void addressBook(HttpServletResponse response,String ids,String saveFileName) throws Exception {
		List<AddressBook> data = this.addressBookService.getAddressBookByIds(ids);
		// 创建workbook
		HSSFWorkbook wb = new HSSFWorkbook();
		// 添加sheet
		HSSFSheet sheet = wb.createSheet("sheet1");
		// 设置每列宽度
		sheet.setColumnWidth(0, 2000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 5000);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 5000);
		sheet.setColumnWidth(7, 10000);
		
		//设置表头和样式
		HSSFRow rowHead = sheet.createRow(0);//0行
		rowHead.setHeight((short) 300);
		
		//设置单元格样式
		HSSFCell cellTitle;
		HSSFCellStyle style = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		style.setWrapText(true);
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
		cellTitle = rowHead.createCell(0);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("ID");
		
		cellTitle = rowHead.createCell(1);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("公司名称");
		
		cellTitle = rowHead.createCell(2);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("联系人");
		
		cellTitle = rowHead.createCell(3);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("电话");
		
		cellTitle = rowHead.createCell(4);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("手机");
		
		cellTitle = rowHead.createCell(5);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("QQ");
		
		cellTitle = rowHead.createCell(6);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("Email");
		
		cellTitle = rowHead.createCell(7);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("备注");
		
		AddressBook pojo;
		HSSFCell cell;
		for (short i = 1; i <= data.size(); i++) {
			 HSSFRow row = sheet.createRow(i);
			row.setHeight((short) 300);
			pojo = data.get(i - 1);
			
			cell = row.createCell(0);
			cell.setCellValue(pojo.getId());

			cell = row.createCell(1);
			cell.setCellValue(pojo.getCompanyName()==null?"":pojo.getCompanyName());

			cell = row.createCell(2);
			cell.setCellValue(pojo.getUserName()==null?"":pojo.getUserName());
			
			cell = row.createCell(3);
			cell.setCellValue(new HSSFRichTextString(pojo.getTelphone()));
			
			cell = row.createCell(4);
			cell.setCellValue(new HSSFRichTextString(pojo.getMobile()));
			
			cell = row.createCell(5);
			cell.setCellValue(new HSSFRichTextString(pojo.getQq()));
			
			cell = row.createCell(6);
			cell.setCellValue(new HSSFRichTextString(pojo.getEmail()));
			
			cell = row.createCell(7);
			cell.setCellValue(new HSSFRichTextString(pojo.getDetail()));
		}
		outExcel(response,wb,saveFileName);
	}
	
	private void product(HttpServletResponse response,String ids,String saveFileName) throws Exception {
		List<Product> data = this.productService.getProductByIds(ids);
		// 创建workbook
		HSSFWorkbook wb = new HSSFWorkbook();
		// 添加sheet
		HSSFSheet sheet = wb.createSheet("sheet1");
		// 设置每列宽度
		sheet.setColumnWidth(0, 2000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 5000);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 5000);
		sheet.setColumnWidth(7, 10000);
		
		//设置表头和样式
		HSSFRow rowHead = sheet.createRow(0);//0行
		rowHead.setHeight((short) 300);
		
		//设置单元格样式
		HSSFCell cellTitle;
		HSSFCellStyle style = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		style.setWrapText(true);
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
		cellTitle = rowHead.createCell(0);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("ID");
		
		cellTitle = rowHead.createCell(1);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("产品货号");
		
		cellTitle = rowHead.createCell(2);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("产品名称");
		
		cellTitle = rowHead.createCell(3);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("是否需要商检");
		
		cellTitle = rowHead.createCell(4);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("退税率");
		
		cellTitle = rowHead.createCell(5);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("海关编码");
		
		cellTitle = rowHead.createCell(6);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("常规材质");
		
		cellTitle = rowHead.createCell(7);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("申报要素");
		
		Product pojo;
		HSSFCell cell;
		for (short i = 1; i <= data.size(); i++) {
			HSSFRow row = sheet.createRow(i);
			row.setHeight((short) 300);
			pojo = data.get(i - 1);
			
			cell = row.createCell(0);
			cell.setCellValue(pojo.getId());

			cell = row.createCell(1);
			cell.setCellValue(pojo.getProductNo()==null?"":pojo.getProductNo());

			cell = row.createCell(2);
			cell.setCellValue(pojo.getName()==null?"":pojo.getName());
			
			cell = row.createCell(3);
			cell.setCellValue(pojo.getInspection()==null?"":pojo.getInspection()==true?"是":"否");
			
			cell = row.createCell(4);
			cell.setCellValue(pojo.getTaxTate()==null?"":pojo.getTaxTate());
			
			cell = row.createCell(5);
			cell.setCellValue(pojo.getCustomsCode()==null?"":pojo.getCustomsCode());
			
			cell = row.createCell(6);
			cell.setCellValue(pojo.getMaterial()==null?"":pojo.getMaterial());
			
			cell = row.createCell(7);
			cell.setCellValue(pojo.getDeclarationElements()==null?"":pojo.getDeclarationElements());
		}
		outExcel(response,wb,saveFileName);		
	}
	private void supplier(HttpServletResponse response,String ids,String saveFileName) throws Exception {		
		List<Supplier> data = this.supplierService.getSupplierByIds(ids);
		// 创建workbook
		HSSFWorkbook wb = new HSSFWorkbook();
		// 添加sheet
		HSSFSheet sheet = wb.createSheet("sheet1");
		// 设置每列宽度
		sheet.setColumnWidth(0, 2000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 5000);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 5000);
		sheet.setColumnWidth(7, 5000);
		sheet.setColumnWidth(8, 5000);
		sheet.setColumnWidth(9, 10000);
		
		//设置表头和样式
		HSSFRow rowHead = sheet.createRow(0);//0行
		rowHead.setHeight((short) 300);
		
		//设置单元格样式
		HSSFCell cellTitle;
		HSSFCellStyle style = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		style.setWrapText(true);
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
		cellTitle = rowHead.createCell(0);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("ID");
		
		cellTitle = rowHead.createCell(1);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("公司名称");
		
		cellTitle = rowHead.createCell(2);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("联系人");
		
		cellTitle = rowHead.createCell(3);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("联系电话");
		
		cellTitle = rowHead.createCell(4);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("手机号码");
		
		cellTitle = rowHead.createCell(5);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("传真号码");
		
		cellTitle = rowHead.createCell(6);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("QQ号码");
		
		cellTitle = rowHead.createCell(7);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("公司网址");
		
		cellTitle = rowHead.createCell(8);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("邮箱地址");
		
		cellTitle = rowHead.createCell(9);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("地址");
		
		Supplier pojo;
		HSSFCell cell;
		for (short i = 1; i <= data.size(); i++) {
			HSSFRow row = sheet.createRow(i);
			row.setHeight((short) 300);
			pojo = data.get(i - 1);
			
			cell = row.createCell(0);
			cell.setCellValue(pojo.getId());

			cell = row.createCell(1);
			cell.setCellValue(pojo.getCompanyName()==null?"":pojo.getCompanyName());

			cell = row.createCell(2);
			cell.setCellValue(pojo.getContact()==null?"":pojo.getContact());
			
			cell = row.createCell(3);
			cell.setCellValue(pojo.getTel()==null?"":pojo.getTel());
			
			cell = row.createCell(4);
			cell.setCellValue(pojo.getMobile()==null?"":pojo.getMobile());
			
			cell = row.createCell(5);
			cell.setCellValue(pojo.getFax()==null?"":pojo.getFax());
			
			cell = row.createCell(6);
			cell.setCellValue(pojo.getQq()==null?"":pojo.getQq());
			
			cell = row.createCell(7);
			cell.setCellValue(pojo.getWebsite()==null?"":pojo.getWebsite());
			
			cell = row.createCell(8);
			cell.setCellValue(pojo.getEmail()==null?"":pojo.getEmail());
			
			cell = row.createCell(9);
			cell.setCellValue(pojo.getAddress()==null?"":pojo.getAddress());
		}
		
		outExcel(response,wb,saveFileName);		
	}
	private void quotation(HttpServletResponse response,String ids,String saveFileName) throws Exception {
		List<SupplierQuotation> data = this.supplierQuotationService.getSupplierQuotationByIds(ids);
		// 创建workbook
		HSSFWorkbook wb = new HSSFWorkbook();
		// 添加sheet
		HSSFSheet sheet = wb.createSheet("sheet1");
		// 设置每列宽度
		sheet.setColumnWidth(0, 2000);
		sheet.setColumnWidth(1, 3000);
		sheet.setColumnWidth(2, 3000);
		sheet.setColumnWidth(3, 3000);
		sheet.setColumnWidth(4, 10000);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 3000);
		sheet.setColumnWidth(7, 3000);
		sheet.setColumnWidth(8, 3000);
		sheet.setColumnWidth(9, 3000);
		sheet.setColumnWidth(10, 3000);
		sheet.setColumnWidth(11, 3000);
		
		//设置表头和样式
		HSSFRow rowHead = sheet.createRow(0);//0行
		rowHead.setHeight((short) 300);
		
		//设置单元格样式
		HSSFCell cellTitle;
		HSSFCellStyle style = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		style.setWrapText(true);
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
		cellTitle = rowHead.createCell(0);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("ID");
		
		cellTitle = rowHead.createCell(1);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("报价时间");
		
		cellTitle = rowHead.createCell(2);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("产品货号");
				
		cellTitle = rowHead.createCell(3);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("产品价格");
		
		cellTitle = rowHead.createCell(4);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("品质规格");
		
		cellTitle = rowHead.createCell(5);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("供应商名称");
				
		cellTitle = rowHead.createCell(6);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("外包装长");
		
		cellTitle = rowHead.createCell(7);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("外包装宽");
		
		cellTitle = rowHead.createCell(8);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("外包装高");
		
		cellTitle = rowHead.createCell(9);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("毛重");
		
		cellTitle = rowHead.createCell(10);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("净重");
		
		cellTitle = rowHead.createCell(11);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("箱个数");
		
		SupplierQuotation pojo;
		HSSFCell cell;
		for (short i = 1; i <= data.size(); i++) {
			HSSFRow row = sheet.createRow(i);
			row.setHeight((short) 300);
			pojo = data.get(i - 1);
			
			cell = row.createCell(0);
			cell.setCellValue(pojo.getId());

			cell = row.createCell(1);
			cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", pojo.getQuoteTime())));

			if(pojo.getProduct()!=null){
				cell = row.createCell(2);
				cell.setCellValue(pojo.getProduct().getProductNo());
			}
			cell = row.createCell(3);
			cell.setCellValue(pojo.getPrice()==null?0:pojo.getPrice());
						
			cell = row.createCell(4);
			cell.setCellValue(pojo.getQualitySpecifications()==null?"":pojo.getQualitySpecifications());

			if(pojo.getSupplier()!=null){
				cell = row.createCell(5);
				cell.setCellValue(pojo.getSupplier().getCompanyName());
			}
			cell = row.createCell(6);
			cell.setCellValue(pojo.getPackingLong()==null?0:pojo.getPackingLong());
			
			cell = row.createCell(7);
			cell.setCellValue(pojo.getPackingWidth()==null?0:pojo.getPackingWidth());
			
			cell = row.createCell(8);
			cell.setCellValue(pojo.getPackingHeight()==null?0:pojo.getPackingHeight());
			
			cell = row.createCell(9);
			cell.setCellValue(pojo.getGrossWeight()==null?0:pojo.getGrossWeight());
			
			cell = row.createCell(10);
			cell.setCellValue(pojo.getNetWeight()==null?0:pojo.getNetWeight());
			
			cell = row.createCell(11);
			cell.setCellValue(pojo.getBoxes());	
			
		}
		
		outExcel(response,wb,saveFileName);
	}
	
	/**
	 * poi不能二次合并
	 * @param response
	 * @param ids
	 * @param saveFileName
	 * @throws Exception
	 */
	private void order(HttpServletResponse response,String ids,String saveFileName) throws Exception {
		saveFileName= "";
		// 先读取模板
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(FileStorage.getRootPath()+"WEB-INF/template/factory.xls")); 
        // 基于模板创建workbook
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        HSSFSheet sheet = workbook.getSheetAt(0);
        
        OrderForm order = null;
		if(StringUtils.isNotBlank(ids)){
			order = this.orderService.getOrderById(Long.parseLong(ids),false);			
		}		
        if(order!=null){
	        // 公司名称
	        HSSFCell cell = sheet.getRow(0).getCell(0);        
	        cell.setCellValue("合同"+order.getOrderCode()+"汇总表");
	        saveFileName = order.getOrderCode();
	        
	        cell = sheet.getRow(2).getCell(2);        
	        cell.setCellValue(order.getCustomerCountry()==null?"":order.getCustomerCountry());	        
	        cell = sheet.getRow(2).getCell(5);        
	        cell.setCellValue(order.getTotalAmount()==null?0:order.getTotalAmount());
	        cell = sheet.getRow(2).getCell(7);        
	        cell.setCellValue(order.getPriceTerms()==null?"":order.getPriceTerms());
	        cell = sheet.getRow(2).getCell(9);        
	        cell.setCellValue(order.getTransactionMethod()==null?"":order.getTransactionMethod());
	        
	        cell = sheet.getRow(3).getCell(2);        
	        cell.setCellValue(order.getCustomerCompany()==null?"":order.getCustomerCompany());
	        cell = sheet.getRow(3).getCell(5);        
	        cell.setCellValue(order.getTotalNumber()==null?0:order.getTotalNumber());
	        cell = sheet.getRow(3).getCell(7);        
	        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", order.getClosingDate())));
	        cell = sheet.getRow(3).getCell(9);        
	        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", order.getDateShipment())));
	        
	        cell = sheet.getRow(4).getCell(2);        
	        cell.setCellValue(order.getCustomerName()==null?"":order.getCustomerName());
	        cell = sheet.getRow(4).getCell(5);        
	        cell.setCellValue(order.getGrossWeight()==null?0:order.getGrossWeight());
	        cell = sheet.getRow(4).getCell(7);        
	        cell.setCellValue(order.getStartShipment()==null?"":order.getStartShipment());
	        cell = sheet.getRow(4).getCell(9);        
	        cell.setCellValue(order.getStatus()==null?"":order.getStatus().getCodeType());
	        
	        cell = sheet.getRow(5).getCell(2);        
	        cell.setCellValue(order.getCustomerGrade()==null?"":order.getCustomerGrade().getCodeName());
	        cell = sheet.getRow(5).getCell(5);        
	        cell.setCellValue(order.getTotatlVolume()==null?0:order.getTotatlVolume());
	        cell = sheet.getRow(5).getCell(7);        
	        cell.setCellValue(order.getEndShipment()==null?"":order.getEndShipment());
	        cell = sheet.getRow(5).getCell(9);        
	        cell.setCellValue((order.getScoring()==null?0:order.getScoring())+"分");
	        
	        cell = sheet.getRow(8).getCell(1);        
	        cell.setCellValue(order.getPaymentAmount1()==null?0:order.getPaymentAmount1());
	        cell = sheet.getRow(8).getCell(3);        
	        cell.setCellValue(order.getPaymentAmount2()==null?0:order.getPaymentAmount2());
	        cell = sheet.getRow(8).getCell(5);        
	        cell.setCellValue(order.getPaymentAmount3()==null?0:order.getPaymentAmount3());
	        cell = sheet.getRow(8).getCell(7);        
	        cell.setCellValue(order.getPaymentAmount4()==null?0:order.getPaymentAmount4());
	        cell = sheet.getRow(8).getCell(9);        
	        cell.setCellValue(order.getPaymentAmount1()+order.getPaymentAmount2()+order.getPaymentAmount3()+order.getPaymentAmount4());
	        
	        cell = sheet.getRow(9).getCell(1);        
	        cell.setCellValue(order.getBankCharges1()==null?0:order.getBankCharges1());
	        cell = sheet.getRow(9).getCell(3);        
	        cell.setCellValue(order.getBankCharges2()==null?0:order.getBankCharges2());
	        cell = sheet.getRow(9).getCell(5);        
	        cell.setCellValue(order.getBankCharges3()==null?0:order.getBankCharges3());
	        cell = sheet.getRow(9).getCell(7);        
	        cell.setCellValue(order.getBankCharges4()==null?0:order.getBankCharges4());
	        cell = sheet.getRow(9).getCell(9);        
	        cell.setCellValue(order.getBankCharges1()+order.getBankCharges2()+order.getBankCharges3()+order.getBankCharges4());
	        
	        cell = sheet.getRow(10).getCell(1);        
	        cell.setCellValue(order.getRealIncome1()==null?0:order.getRealIncome1());
	        cell = sheet.getRow(10).getCell(3);        
	        cell.setCellValue(order.getRealIncome2()==null?0:order.getRealIncome2());
	        cell = sheet.getRow(10).getCell(5);        
	        cell.setCellValue(order.getRealIncome3()==null?0:order.getRealIncome3());
	        cell = sheet.getRow(10).getCell(7);        
	        cell.setCellValue(order.getRealIncome4()==null?0:order.getRealIncome4());
	        cell = sheet.getRow(10).getCell(9);        
	        cell.setCellValue(order.getRealIncome1()+order.getRealIncome2()+order.getRealIncome3()+order.getRealIncome4());
	        
	        cell = sheet.getRow(11).getCell(1);        
	        cell.setCellValue(order.getExchangeIncome1()==null?0:order.getExchangeIncome1());
	        cell = sheet.getRow(11).getCell(3);        
	        cell.setCellValue(order.getExchangeIncome2()==null?0:order.getExchangeIncome2());
	        cell = sheet.getRow(11).getCell(5);        
	        cell.setCellValue(order.getExchangeIncome3()==null?0:order.getExchangeIncome3());
	        cell = sheet.getRow(11).getCell(7);        
	        cell.setCellValue(order.getExchangeIncome4()==null?0:order.getExchangeIncome4());
	        cell = sheet.getRow(11).getCell(9);        
	        cell.setCellValue(order.getExchangeIncome1()+order.getExchangeIncome2()+order.getExchangeIncome3()+order.getExchangeIncome4());
	        
	        cell = sheet.getRow(12).getCell(1);        
	        cell.setCellValue(order.getCustomerAccount1()==null?"":order.getCustomerAccount1());
	        cell = sheet.getRow(12).getCell(3);        
	        cell.setCellValue(order.getCustomerAccount2()==null?"":order.getCustomerAccount2());
	        cell = sheet.getRow(12).getCell(5);        
	        cell.setCellValue(order.getCustomerAccount3()==null?"":order.getCustomerAccount3());
	        cell = sheet.getRow(12).getCell(7);        
	        cell.setCellValue(order.getCustomerAccount4()==null?"":order.getCustomerAccount4());
	        
	        cell = sheet.getRow(13).getCell(1);        
	        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", order.getPaymentTime1())));
	        cell = sheet.getRow(13).getCell(3);        
	        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", order.getPaymentTime2())));
	        cell = sheet.getRow(13).getCell(5);        
	        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", order.getPaymentTime3())));
	        cell = sheet.getRow(13).getCell(7);        
	        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", order.getPaymentTime4())));
	        
	        //工厂付款情况
	        List<FactoryPayment> factoryList = order.getFactoryList();
	        int factoryMaxRow=10;//默认最多十行记录
	        int startRow=16;
	        for(int i=0;i<factoryList.size();i++){
	        	if(i>=factoryMaxRow){//超过十行的处理
//	        		HSSFRow newRow = sheet.createRow(startRow);
//	        		
//	        		HSSFCellStyle cellstyle = workbook.createCellStyle();	        			        		
//	        		cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//	        		cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//	        		cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//	        		cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//	        			        		
//	                cell = newRow.createCell(0);cell.setCellStyle(sheet.getRow(startRow-2).getCell(0).getCellStyle());
//	    			cell.setCellValue(factoryList.get(i).getCooperFactory());
//	    			cell = newRow.createCell(1);cell.setCellStyle(sheet.getRow(startRow-2).getCell(1).getCellStyle());
//			        cell.setCellValue(factoryList.get(i).getAmount());
//			        cell = newRow.createCell(2);cell.setCellStyle(sheet.getRow(startRow-2).getCell(2).getCellStyle());
//			        cell.setCellValue(factoryList.get(i).getTerms());
//			        cell = newRow.createCell(3);cell.setCellStyle(sheet.getRow(startRow-2).getCell(3).getCellStyle());
//			        cell.setCellValue(factoryList.get(i).getPayment1());
//			        cell = newRow.createCell(4);cell.setCellStyle(sheet.getRow(startRow-2).getCell(4).getCellStyle());
//			        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", factoryList.get(i).getPaymentDate1())));
//			        cell = newRow.createCell(5);cell.setCellStyle(sheet.getRow(startRow-2).getCell(5).getCellStyle());
//			        cell.setCellValue(factoryList.get(i).getPayment2());
//			        cell = newRow.createCell(6);cell.setCellStyle(sheet.getRow(startRow-2).getCell(6).getCellStyle());
//			        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", factoryList.get(i).getPaymentDate2())));
//			        cell = newRow.createCell(7);cell.setCellStyle(sheet.getRow(startRow-2).getCell(7).getCellStyle());
//			        cell.setCellValue(factoryList.get(i).getPayment3());
//			        cell = newRow.createCell(8);cell.setCellStyle(sheet.getRow(startRow-2).getCell(8).getCellStyle());
//			        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", factoryList.get(i).getPaymentDate3())));
//			        cell = newRow.createCell(9);cell.setCellStyle(sheet.getRow(startRow-2).getCell(9).getCellStyle());
//			        cell.setCellValue(factoryList.get(i).getTaxRevenue());
	        	}else{
		        	cell = sheet.getRow(startRow).getCell(0);        
			        cell.setCellValue(factoryList.get(i).getCooperFactory()==null?"":factoryList.get(i).getCooperFactory());
			        cell = sheet.getRow(startRow).getCell(1);        
			        cell.setCellValue(factoryList.get(i).getAmount()==null?0:factoryList.get(i).getAmount());
			        cell = sheet.getRow(startRow).getCell(2);        
			        cell.setCellValue(factoryList.get(i).getTerms()==null?"":factoryList.get(i).getTerms());
			        cell = sheet.getRow(startRow).getCell(3);        
			        cell.setCellValue(factoryList.get(i).getPayment1()==null?0:factoryList.get(i).getPayment1());
			        cell = sheet.getRow(startRow).getCell(4);        
			        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", factoryList.get(i).getPaymentDate1())));
			        cell = sheet.getRow(startRow).getCell(5);        
			        cell.setCellValue(factoryList.get(i).getPayment2()==null?0:factoryList.get(i).getPayment2());
			        cell = sheet.getRow(startRow).getCell(6);        
			        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", factoryList.get(i).getPaymentDate2())));
			        cell = sheet.getRow(startRow).getCell(7);        
			        cell.setCellValue(factoryList.get(i).getPayment3()==null?0:factoryList.get(i).getPayment3());
			        cell = sheet.getRow(startRow).getCell(8);        
			        cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy.MM.dd", factoryList.get(i).getPaymentDate3())));
			        cell = sheet.getRow(startRow).getCell(9);        
			        cell.setCellValue(factoryList.get(i).getTaxRevenue()==null?0:factoryList.get(i).getTaxRevenue());
	        	}
	        	//合并单元格
	            //sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1"));
	        	
	        	startRow++;
	        }
	        //收入支出
	        startRow=startRow>26?startRow:27;
	        int ioStartRow = startRow+1;
	        factoryMaxRow=2;
	        
	        List<IncomeExpenses> io = order.getIncomeList();
	        List<IncomeExpenses> in = new ArrayList<IncomeExpenses>();
	        List<IncomeExpenses> out = new ArrayList<IncomeExpenses>();
	        Double amount_in = 0.0;//总收入
	        Double amount_out = 0.0;//总支出    
	        for(IncomeExpenses ie:io){
	        	if(ie.getType()){
	        		in.add(ie);
	        		amount_in=amount_in+(ie.getAmount()==null?0:ie.getAmount());
	        	}else{
	        		out.add(ie);
	        		amount_out=amount_out+(ie.getAmount()==null?0:ie.getAmount());
	        	}
	        }
	            		
	        for(int i=0;i<in.size();i++){
	        	if(i>=factoryMaxRow){//插入行
	        		sheet.shiftRows(startRow, sheet.getLastRowNum(), 1,true,false);
	        		HSSFRow source = sheet.getRow(startRow-1);
	        		HSSFRow newRow = sheet.createRow(startRow);	      
	        		//复制格式
	        		for (int k = source.getFirstCellNum(), columnNo = source.getLastCellNum(); k < columnNo; k++) {
	        			HSSFCell sourceCell = source.getCell(k);
	        			HSSFCell targetCell = newRow.createCell(k);
	        			targetCell.setCellStyle(sourceCell.getCellStyle());
//	        		 	targetCell.setCellValue("cell," + i);
	        		}	        			        		
	        		sheet.addMergedRegion(CellRangeAddress.valueOf("B"+(startRow+1)+":E"+(startRow+1)));	        
	    	        sheet.addMergedRegion(CellRangeAddress.valueOf("F"+(startRow+1)+":I"+(startRow+1)));
	    	        cell = sheet.getRow(startRow).getCell(1);        
			        cell.setCellValue(in.get(i).getCooperFactory()==null?"":in.get(i).getCooperFactory());
			        cell = sheet.getRow(startRow).getCell(5);        
			        cell.setCellValue(in.get(i).getAmount()==null?0:in.get(i).getAmount());				        			          
	        	}else{
	        		cell = sheet.getRow(startRow).getCell(1);        
			        cell.setCellValue(in.get(i).getCooperFactory()==null?"":in.get(i).getCooperFactory());
			        cell = sheet.getRow(startRow).getCell(5);        
			        cell.setCellValue(in.get(i).getAmount()==null?0:in.get(i).getAmount());			        
	        	}	        	
	        	startRow++;
	        }
	        
	        cell = sheet.getRow(ioStartRow).getCell(9);        
	        cell.setCellValue(amount_in);
	        
	        sheet.addMergedRegion(CellRangeAddress.valueOf("A"+ioStartRow+":A"+(in.size()>factoryMaxRow?ioStartRow+in.size()-factoryMaxRow+1:ioStartRow+1)));
	        sheet.addMergedRegion(CellRangeAddress.valueOf("J"+(ioStartRow)+":K"+ioStartRow));
	        sheet.addMergedRegion(CellRangeAddress.valueOf("J"+(ioStartRow+1)+":K"+(in.size()>factoryMaxRow?ioStartRow+in.size()-factoryMaxRow+1:ioStartRow+1)));
	        //支出
	        startRow=in.size()>factoryMaxRow?ioStartRow+in.size():ioStartRow+factoryMaxRow;    
	        ioStartRow = startRow+1;
	        factoryMaxRow=2;
	        for(int i=0;i<out.size();i++){
	        	if(i>=factoryMaxRow){//超过默认行插入新行
	        		sheet.shiftRows(startRow, sheet.getLastRowNum(), 1,true,false);
	        		HSSFRow source = sheet.getRow(startRow-1);
	        		HSSFRow newRow = sheet.createRow(startRow);	      
	        		//复制格式
	        		for (int k = source.getFirstCellNum(), columnNo = source.getLastCellNum(); k < columnNo; k++) {
	        			HSSFCell sourceCell = source.getCell(k);
	        			HSSFCell targetCell = newRow.createCell(k);
	        			targetCell.setCellStyle(sourceCell.getCellStyle());
	        		}
	        		sheet.addMergedRegion(CellRangeAddress.valueOf("B"+(startRow+1)+":E"+(startRow+1)));	        
	    	        sheet.addMergedRegion(CellRangeAddress.valueOf("F"+(startRow+1)+":I"+(startRow+1)));
	    	        cell = sheet.getRow(startRow).getCell(1);        
			        cell.setCellValue(out.get(i).getCooperFactory()==null?"":out.get(i).getCooperFactory());
			        cell = sheet.getRow(startRow).getCell(5);        
			        cell.setCellValue(out.get(i).getAmount()==null?0:out.get(i).getAmount());	
	        	}else{
	        		cell = sheet.getRow(startRow).getCell(1);        
			        cell.setCellValue(out.get(i).getCooperFactory()==null?"":out.get(i).getCooperFactory());
			        cell = sheet.getRow(startRow).getCell(5);        
			        cell.setCellValue(out.get(i).getAmount()==null?0:out.get(i).getAmount());			        
	        	}
	        	startRow++;
	        }
	        cell = sheet.getRow(ioStartRow).getCell(9);        
	        cell.setCellValue(amount_out);
//	        System.out.println("A"+ioStartRow+":A"+(out.size()>factoryMaxRow?ioStartRow+out.size()-factoryMaxRow+1:ioStartRow+1));
	        sheet.addMergedRegion(CellRangeAddress.valueOf("A"+ioStartRow+":A"+(out.size()>factoryMaxRow?ioStartRow+out.size()-factoryMaxRow+1:ioStartRow+1)));	  
	        sheet.addMergedRegion(CellRangeAddress.valueOf("J"+(ioStartRow)+":K"+ioStartRow));
	        sheet.addMergedRegion(CellRangeAddress.valueOf("J"+(ioStartRow+1)+":K"+(out.size()>factoryMaxRow?ioStartRow+out.size()-factoryMaxRow+1:ioStartRow+1)));
	        
	        ioStartRow=(out.size()>factoryMaxRow)?ioStartRow+out.size()-factoryMaxRow+1:ioStartRow+1;
//	        System.out.println(ioStartRow+","+startRow);
	        cell = sheet.getRow(ioStartRow).getCell(9);        
	        cell.setCellValue(amount_in-amount_out);	        
        }
//        "MAX"+DateUtil.getDateTimeFormString("yyyy",new Date())+"-"+
        saveFileName = saveFileName+".xls";  
        outExcel(response,workbook,saveFileName);		
	}
	
	 
	private void send(HttpServletResponse response,String ids,String saveFileName) throws Exception {
		List<SendExpress> data = this.sendService.getSendExpressByIds(ids);
		// 创建workbook
		HSSFWorkbook wb = new HSSFWorkbook();
		// 添加sheet
		HSSFSheet sheet = wb.createSheet("sheet1");
		// 设置每列宽度
		sheet.setColumnWidth(0, 2000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 5000);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 5000);
		sheet.setColumnWidth(7, 5000);
		sheet.setColumnWidth(8, 5000);
		sheet.setColumnWidth(9, 5000);
		sheet.setColumnWidth(10, 5000);
		sheet.setColumnWidth(11, 5000);
		
		//设置表头和样式
		HSSFRow rowHead = sheet.createRow(0);//0行
		rowHead.setHeight((short) 300);
		
		//设置单元格样式
		HSSFCell cellTitle;
		HSSFCellStyle style = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		style.setWrapText(true);
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
		cellTitle = rowHead.createCell(0);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("ID");
		
		cellTitle = rowHead.createCell(1);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("快递单号");
		
		cellTitle = rowHead.createCell(2);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("快递公司");
		
		cellTitle = rowHead.createCell(3);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("重量");
		
		cellTitle = rowHead.createCell(4);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("价格");
		
		cellTitle = rowHead.createCell(5);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("收件人国家");
		
		cellTitle = rowHead.createCell(6);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("寄件日期");
		
		cellTitle = rowHead.createCell(7);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("收件人");
		
		cellTitle = rowHead.createCell(8);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("寄件内容");
		
		cellTitle = rowHead.createCell(9);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("寄件公司");
		
		cellTitle = rowHead.createCell(10);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("寄件人地址");
		
		cellTitle = rowHead.createCell(11);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("寄件人电话");
		
		SendExpress pojo;
		HSSFCell cell;
		for (short i = 1; i <= data.size(); i++) {
			HSSFRow row = sheet.createRow(i);
			row.setHeight((short) 300);
			pojo = data.get(i - 1);
			
			cell = row.createCell(0);
			cell.setCellValue(pojo.getId());

			cell = row.createCell(1);
			cell.setCellValue(pojo.getExpressNumber()==null?"":pojo.getExpressNumber());

			cell = row.createCell(2);
			cell.setCellValue(pojo.getExpressCompany()==null?"":pojo.getExpressCompany().getCodeName());
			
			cell = row.createCell(3);
			cell.setCellValue(pojo.getWeight()==null?0:pojo.getWeight());
			
			cell = row.createCell(4);
			cell.setCellValue(pojo.getPrice()==null?0:pojo.getPrice());
			
			cell = row.createCell(5);
			cell.setCellValue(pojo.getSender()==null?"":pojo.getSender());
			
			cell = row.createCell(6);
			cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy-MM-dd HH:mm:ss", pojo.getSendDate())));
			
			cell = row.createCell(7);
			cell.setCellValue(pojo.getUser()==null?"":pojo.getUser().getUserName());
			
			cell = row.createCell(8);
			cell.setCellValue(pojo.getContent()==null?"":pojo.getContent());
			
			cell = row.createCell(9);
			cell.setCellValue(pojo.getCompany()==null?"":pojo.getCompany());
			
			cell = row.createCell(10);
			cell.setCellValue(pojo.getAddress()==null?"":pojo.getAddress());
			
			cell = row.createCell(11);
			cell.setCellValue(pojo.getTelphone()==null?"":pojo.getTelphone());
			
		}
		outExcel(response,wb,saveFileName);
	}
	private void receive(HttpServletResponse response,String ids,String saveFileName) throws Exception {
		List<ReceiveExpress> data = this.getReceiveService().getReceiveExpressByIds(ids);
		// 创建workbook
		HSSFWorkbook wb = new HSSFWorkbook();
		// 添加sheet
		HSSFSheet sheet = wb.createSheet("sheet1");
		// 设置每列宽度
		sheet.setColumnWidth(0, 2000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 5000);
		sheet.setColumnWidth(4, 5000);
		sheet.setColumnWidth(5, 5000);
		sheet.setColumnWidth(6, 5000);
		sheet.setColumnWidth(7, 5000);
		sheet.setColumnWidth(8, 5000);
		sheet.setColumnWidth(9, 5000);
		sheet.setColumnWidth(10, 5000);
		sheet.setColumnWidth(10, 5000);
		sheet.setColumnWidth(11, 5000);
		
		//设置表头和样式
		HSSFRow rowHead = sheet.createRow(0);//0行
		rowHead.setHeight((short) 300);
		
		//设置单元格样式
		HSSFCell cellTitle;
		HSSFCellStyle style = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		style.setWrapText(true);
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
		cellTitle = rowHead.createCell(0);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("ID");
		
		cellTitle = rowHead.createCell(1);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("快递单号");
		
		cellTitle = rowHead.createCell(2);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("快递公司");
		
		cellTitle = rowHead.createCell(3);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("重量");
		
		cellTitle = rowHead.createCell(4);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("价格");
		
		cellTitle = rowHead.createCell(5);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("业务员");
		
		cellTitle = rowHead.createCell(6);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("收件日期");
		
		cellTitle = rowHead.createCell(7);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("收件内容");
		
		cellTitle = rowHead.createCell(8);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("寄件人");
		
		cellTitle = rowHead.createCell(9);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("寄件公司");
		
		cellTitle = rowHead.createCell(9);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("寄件国家");
		
		cellTitle = rowHead.createCell(10);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("寄件人电话");
				
		cellTitle = rowHead.createCell(11);
		cellTitle.setCellStyle(style);
		cellTitle.setCellValue("寄件人地址");
		
		ReceiveExpress pojo;
		HSSFCell cell;
		for (short i = 1; i <= data.size(); i++) {
			HSSFRow row = sheet.createRow(i);
			row.setHeight((short) 300);
			pojo = data.get(i - 1);
			
			cell = row.createCell(0);
			cell.setCellValue(pojo.getId());

			cell = row.createCell(1);
			cell.setCellValue(pojo.getExpressNumber()==null?"":pojo.getExpressNumber());

			cell = row.createCell(2);
			cell.setCellValue(pojo.getExpressCompany()==null?"":pojo.getExpressCompany().getCodeName());
			
			cell = row.createCell(3);
			cell.setCellValue(pojo.getWeight()==null?0:pojo.getWeight());
			
			cell = row.createCell(4);
			cell.setCellValue(pojo.getPrice()==null?0:pojo.getPrice());
			
			cell = row.createCell(5);
			cell.setCellValue(pojo.getUser()==null?"":pojo.getUser().getUserName());
			
			cell = row.createCell(6);
			cell.setCellValue(new HSSFRichTextString(DateUtil.getDateTimeFormString("yyyy-MM-dd HH:mm:ss",pojo.getReceiveDate())));
			
			cell = row.createCell(7);
			cell.setCellValue(pojo.getContent()==null?"":pojo.getContent());
			
			cell = row.createCell(8);
			cell.setCellValue(pojo.getSender()==null?"":pojo.getSender());
			
			cell = row.createCell(9);
			cell.setCellValue(pojo.getCompany()==null?"":pojo.getCompany());
			
			cell = row.createCell(10);
			cell.setCellValue(pojo.getCountry()==null?"":pojo.getCountry());
			
			cell = row.createCell(11);
			cell.setCellValue(pojo.getTelphone()==null?"":pojo.getTelphone());
			
			cell = row.createCell(12);
			cell.setCellValue(pojo.getAddress()==null?"":pojo.getAddress());
		}
		outExcel(response,wb,saveFileName);
	}
	private void qlist(HttpServletResponse response,Company company,String ids,String saveFileName) throws Exception {
		
		// 先读取模板
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(FileStorage.getRootPath()+"WEB-INF/template/quotation.xls")); 
		
		// 基于模板创建workbook
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        HSSFSheet sheet = workbook.getSheetAt(0);
        if(company!=null){
	        // 公司名称
	        HSSFCell cell = sheet.getRow(0).getCell(1);        
//	        cell.setCellValue("       FUZHOU MAXOFEI ELECTRICAL APPLIANCES CO.,LTD"); 
//	        // 电话 传真
//	        cell = sheet.getRow(1).getCell(0);
//            cell.setCellValue("Tel："+company.getTel()+"         Fax："+company.getFax());
//            
//            cell = sheet.getRow(2).getCell(0);
//	        cell.setCellValue("Email: "+company.getEmail());
//	        
//	        cell = sheet.getRow(3).getCell(0);
//	        cell.setCellValue("Website: "+company.getWebsite());
//	        
//	        cell = sheet.getRow(4).getCell(0);
//	        cell.setCellValue("Add: "+company.getAddress()+", Post Code:"+company.getZipcode());
//	        
//	        cell = sheet.getRow(10).getCell(0);
//	        cell.setCellValue("From: Fuzhou Maxofei Electrical Appliances Co.,Ltd / Will");
//	        
	        cell = sheet.getRow(10).getCell(11);
	        SimpleDateFormat sdf = new SimpleDateFormat("MMM dd'th', yyyy",Locale.ENGLISH);  
	        cell.setCellValue("Date: "+sdf.format(new Date()));
	        
        }

        //插入图片
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();        
        ByteArrayOutputStream byteArrayOut = null;
        BufferedImage bufferImg = null; 
            
        
		QIndex qindex = this.quotationService.getQIndexById(Long.parseLong(ids));
		if(qindex!=null){			
			int maxRow =43;
			int startRow =13;
			int startCol = 0;
			HSSFCell cell;
			List<QList> data = this.quotationService.getQListByQIndexId(qindex.getId());
			String imgUrl = "";
			for(QList list:data){
				startCol = 0;
				
				cell = sheet.getRow(startRow).getCell(startCol);        
		        cell.setCellValue(list.getProductNo());
		        startCol++;
		        
		        if(list.getProduct()!=null){
		        	imgUrl = ProductFileService.Product.getRootPath() + list.getProduct().getImageUri();
		        }else{
		        	imgUrl = ProductFileService.Product.getRootPath() + list.getImageUri();
		        }
//		        System.out.println(imgUrl);
		        File img = new File(imgUrl);
		        if(img.exists()){
			        //插入产品图片
			        HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,995,248,(short)startCol, startRow,(short)startCol,startRow);
			        try{
			        	bufferImg = ImageIO.read(img);
			        	byteArrayOut = new ByteArrayOutputStream();
			        	ImageIO.write(bufferImg,"jpg",byteArrayOut);
			        	patriarch.createPicture(anchor , workbook.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
			        }catch(IOException io){
			            io.printStackTrace();
			        }     
		        }
		        startCol=startCol+2;
		        
		        cell = sheet.getRow(startRow).getCell(startCol);
		        cell.setCellValue(list.getPackL()==null?0:list.getPackL());
		        startCol++;
		        
		        cell = sheet.getRow(startRow).getCell(startCol);
		        cell.setCellValue(list.getPackW()==null?0:list.getPackW());
		        startCol++;
		        
		        cell = sheet.getRow(startRow).getCell(startCol);
		        cell.setCellValue(list.getPackH()==null?0:list.getPackH());
		        startCol++;
		        
		        cell = sheet.getRow(startRow).getCell(startCol);
		        cell.setCellValue(list.getCbm()==null?0:list.getCbm());
		        startCol++;
		        
		        cell = sheet.getRow(startRow).getCell(startCol);
		        cell.setCellValue(list.getPcs()==null?0:list.getPcs());
		        startCol++;
		        
		        cell = sheet.getRow(startRow).getCell(startCol);
		        cell.setCellValue(list.getGrossWeight()==null?0:list.getGrossWeight());
		        startCol++;
		        
		        cell = sheet.getRow(startRow).getCell(startCol);
		        cell.setCellValue(list.getNetWeight()==null?0:list.getNetWeight());
		        startCol = startCol+6;
		        
		        cell = sheet.getRow(startRow).getCell(startCol);
		        cell.setCellValue(list.getPrice()==null?0:list.getPrice());
		        startCol = startCol+5;
		        
		        cell = sheet.getRow(startRow).getCell(startCol);
		        cell.setCellValue(list.getSupplier()==null?"":list.getSupplier());
		        
		        startRow++;
		        
		        maxRow--;
		        if(maxRow<0){
		        	break;
		        }
			}
		}
		
		outExcel(response,workbook,saveFileName);
	}
	private SupplierQuotationService supplierQuotationService;
	private AddressBookService addressBookService;
	private ProductService productService;
	private OrderService orderService;
	private SupplierService supplierService;
	private ReceiveService receiveService;
	private SendService sendService;
	private CompanyService companyService;
	private QuotationService quotationService;
	
	public CompanyService getCompanyService() {
		return companyService;
	}

	public void setCompanyService(CompanyService companyService) {
		this.companyService = companyService;
	}

	public QuotationService getQuotationService() {
		return quotationService;
	}

	public void setQuotationService(QuotationService quotationService) {
		this.quotationService = quotationService;
	}

	public void setSupplierQuotationService(
			SupplierQuotationService supplierQuotationService) {
		this.supplierQuotationService = supplierQuotationService;
	}

	public void setAddressBookService(AddressBookService addressBookService) {
		this.addressBookService = addressBookService;
	}

	public void setProductService(ProductService productService) {
		this.productService = productService;
	}

	public OrderService getOrderService() {
		return orderService;
	}

	public void setOrderService(OrderService orderService) {
		this.orderService = orderService;
	}

	public SupplierService getSupplierService() {
		return supplierService;
	}

	public void setSupplierService(SupplierService supplierService) {
		this.supplierService = supplierService;
	}

	public ReceiveService getReceiveService() {
		return receiveService;
	}

	public void setReceiveService(ReceiveService receiveService) {
		this.receiveService = receiveService;
	}

	public SendService getSendService() {
		return sendService;
	}

	public void setSendService(SendService sendService) {
		this.sendService = sendService;
	}

	public SupplierQuotationService getSupplierQuotationService() {
		return supplierQuotationService;
	}

	public AddressBookService getAddressBookService() {
		return addressBookService;
	}

	public ProductService getProductService() {
		return productService;
	}
	
}
